Database capacity estimation for database sizing

ABSTRACT

A method and system for sizing databases. Multiple patterns of historical capacity usage by multiple databases in a computer system are identified. The multiple patterns are statistically significant for multiple characteristics due to the multiple databases including an input value of each characteristic of multiple characteristics pertaining to utilization of the multiple databases. A weighted value of capacity usage is computed as ACP*FC*PC, wherein ACP=an average value of historical capacity points for each pattern, FC=fraction of the characteristics associated with each pattern, and PC=a fraction of the multiple databases used for each pattern. One or more current databases are sized via utilization of the computed total value of capacity usage. A virtual machine or a new server of the computer system is created. The virtual machine or new server includes the sized one or more current databases.

This application is a continuation application claiming priority to Ser.No. 14/243,948, filed Apr. 3, 2014, now U.S. Pat. No. 10,394,772 issuedAug. 27, 2019.

TECHNICAL FIELD

The present invention relates generally to database sizing, and morespecifically to estimation of requisite database capacity usage.

BACKGROUND

When a new database is required, it can be difficult to predict theamount of memory and processors needed to provide the requiredperformance for its intended application. It can also be difficult tocorrelate changes to structure and use with their impact on performance.It was known to estimate a requisite amount of memory and processor fora new database based on a comparison to memory and processor in existingdatabases and the resultant performance. It was also known to adjust theamount of memory and processor for a database over time, based on themeasured performance during actual use. However, it is helpful to make agood estimate of the requisite amounts of memory and processor wheninitially designing a database to provide good performance without theexpense of excess memory or processors or the need to upgrade soonafterwards.

McDonald et el. (U.S. Pat. No. 7,062,426) disclose a sizing tool whichcorrelates each operating system to each user type which is correlatedto each application and increased by the number of particular user typesand the number of applications involved in order to establish the totalmemory requirements.

McDonald et el. (U.S. Pat. No. 6,963,828) disclose a sizing tool whichallocates the optimum number of users to each server farm while alsoproviding an optimized configuration of server farms having a desiredavailability level and suitable redundancy to fulfill the requirementsand needs in a customer's enterprise.

Lee et el. (U.S. Pat. No. 7,047,177) disclose a sizing tool forproviding the most suitable configuration for each specializedenterprise of a customer.

Eberhard, et al. (U.S. Pat. No. 6,003,022) estimates the cost ofexecution related to specific database transactions, includingpredicting the cost of execution via individual database transactions.

BRIEF SUMMARY

The present invention provides a method and an associated system forsizing databases. One or more processors receive an input value of eachcharacteristic of multiple characteristics pertaining to utilization ofmultiple databases in a computer system. The one or more processorsidentify multiple patterns of historical capacity usage by the multipledatabases, wherein the multiple patterns are statistically significantfor the multiple characteristics and encompass the input values, andwherein the capacity usage is memory usage or central processing unit(CPU) usage. The one or more processors compute, for each pattern, aweighted value of capacity usage as an average value of historicalcapacity usage for each pattern weighted by a function of a fraction ofthe multiple databases used for each pattern. The one or more processorscompute a total value of capacity usage as a sum of the weighted valuesof capacity usage. The one or more processors perform sizing one or morecurrent databases, wherein the sizing utilizes the computed total valueof capacity usage. The database sizing may be based on query complexityin dependence on rates of occurrence of queries to the historicdatabases and to the current database.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates a computer system for sizing databases, in accordancewith embodiments of the present invention.

FIG. 2 is a flow chart of a method for sizing databases, in accordancewith embodiments of the present invention.

FIG. 3 is a flow chart describing deletion of at least onecharacteristic previously used to size current databases via the methodof FIG. 2, and resizing the current databases in consideration of thedeleted at least one characteristic, in accordance with embodiments ofthe present invention.

FIG. 4 is a flow chart of a method for sizing a capacity usage of a newdatabase based on query complexity, in accordance with embodiments ofthe present invention.

FIG. 5 illustrates a computer apparatus used by the computer system ofFIG. 1 for sizing databases, in accordance with embodiments of thepresent invention.

DETAILED DESCRIPTION

FIG. 1 illustrates a computer system 10 for sizing databases, inaccordance with embodiments of the present invention. The computersystem 10 comprises a processor 11, an input device 12 coupled to theprocessor 11, an output device 13 coupled to the processor 11, a memory14 coupled to the processor 11, and a storage device 15 each coupled tothe processor 11. The input device 12 may be, inter alia, a keyboard, amouse, etc. The output device 13 may be, inter alia, a printer, aplotter, a computer screen, a magnetic tape, a removable hard disk, afloppy disk, etc. The memory 14 may be, inter alia, random access memory(RAM). The storage device 15 may be, inter alia, a hard disk, a floppydisk, a magnetic tape, an optical storage such as a compact disc (CD) ora digital video disc (DVD), etc. The storage device 15 stores computerprograms, including database sizing program 17, configured to beexecuted by the processor 11 via the memory 14 to manage capacity usageby databases. The storage device 15 also includes historic databases 18and current databases 19. The storage device 15 also includes historicdatabases 18 and current databases 19.

The database sizing program 17 includes program instructions thatutilize data in the historic databases 18 to size the current databases19. The current databases 19 are defined as either new databases thathave not been previously sized or existing databases that werepreviously sized and are currently being resized. The historic databases18 are defined as previously used databases whose historical data isbeing used to size or resize the current databases 19.

The processor 11 executes the database sizing program 17. The memory 14may store input data 16 required by the program 17. The output device 13displays output from the programs 17. The storage device 15 is acomputer readable storage device (such as a magnetic disk orsemiconductor memory), or alternatively a plurality of tangible computerreadable storage devices, storing the programs 17 for execution by theprocessor 11, or alternatively by one or more processors (e.g., aplurality of processors), via the memory 14. Generally, the program 17may be stored on a single storage device or may be stored by beingdistributed among a plurality of storage devices in any manner known inthe art. A computer program product (or, alternatively, an article ofmanufacture) of the computer system 10 may comprise the storage device15, or a plurality of storage devices, storing the program 17 forexecution by the processor 11, or alternatively by a plurality ofprocessors, via the memory 14. The term “computer readable storagedevice” does not mean a signal propagation medium such as a coppertransmission cable, an optical transmission fiber, or a wirelesstransmission media.

While FIG. 1 shows the computer system 10 as a particular configurationof hardware and software, any configuration of hardware and software, aswould be known to a person of ordinary skill in the art, may be utilizedfor the purposes stated supra in conjunction with the particularcomputer system 10 of FIG. 1.

The present invention includes a sizing engine, namely the databasesizing program 17. The sizing engine uses historical capacity data inthe historic databases 18 for patterns of capacity usage by databaseswith similar characteristics to estimate a sizing of capacity for a newor changed database, such as a database in a Relational DatabaseManagement System (RDBMS). In one embodiment, the capacity that is sizedis memory capacity or central processing unit (CPU) capacity. Theestimate of capacity sizing by the sizing engine can be fed into aprovisioning system (e.g., a cloud based technology system) to create avirtual machine or a new server.

This description of the present invention expresses capacity in terms ofcapacity points which can be translated, via a conversion factor, tocapacity units for specific hardware such as units of megabytes,gigabytes, etc. for memory capacity, or units of megahertz, gigahertz,etc. for CPU capacity, or a relative performance metric (e.g., rPerf).

The historical capacity data of the historic databases 18 is stored in adata repository and includes enough data to be statistically relevant.Thus, the historical capacity data includes an adequate sample ofproduction database usage data collected over a period of time.

The sizing engine includes sub-processes pertaining to sizingcalculations, data analytics, and change triggers.

The sizing calculations produce capacity sizing estimates based onsystem input received and weighted capacity values, as discussed infrain conjunction with FIG. 2.

The data analytics include adding, deleting, and/or modifying weightedvalues assigned to pre-defined database characteristics, as discussedinfra in conjunction with FIG. 3. Characteristics are weighted byanalyzing patterns and relationships between the characteristic andusage. The data analytics also include a methodology for completelyadding or removing a defined database characteristic.

The change triggers include triggers to change weighted capacity values,which enables continuous refinement of the sizing engine to increase theaccuracy of the capacity estimates.

The present invention includes a set of characteristics pertaining toutilization of the historic databases 18. The list of characteristicscan change as new characteristics are identified or as oldcharacteristics are no longer showing statistically relevantrelationships to capacity. This relevance is based on the analyticsfound in the change triggers sub-process. The following list showstypical characteristics that may be applied to the sizing engine input:

-   -   1. RDBMS: Type of RDBMS (Ex. DB2, Oracle)    -   2. Version: Version of RDBMS (Specific versions may have greater        capacity requirements)    -   3. Database Size: Size of Database    -   4. Query Complexity. The rate of database queries impacts memory        and processor requirements of the database, such that a greater        rate of database queries requires more memory and processor to        meet a performance requirement. However, the more complex the        database queries the greater the requirement of memory and        processors, and vice versa. Therefore, in estimating the        requisite amount of memory and processor for a database, the        database sizing program 17 considers not only the rate of        database queries, but also the complexity of the database        queries.        -   a. % of Low complexity/highly tuned queries (such as simple            select/update/insert/delete on indexed tables)        -   b. % of Medium complexity/tuned queries (such as two-way            joins, two-way sub-queries, simple DML/DDL)        -   c. % of High complexity/tuned queries (such as multiple            joins, un-indexed scans, aggregated joins, multiple            sub-queries)    -   5. Bufferpool Size(s)    -   6. Database Type: Type of database (Ex. OLAP, Data Warehouse)    -   7. Total number of database users    -   8. Total number of database users during peak time    -   9. Average number of database users    -   10. Average number of transactions per hour    -   11. Maximum number of transactions per hour

The preceding characteristics are exemplary and the scope of the presentinvention is not limited to the preceding characteristic and may includeany other characteristics that are statistically relevant forcalculating or estimating capacity.

FIG. 2 is a flow chart of a method for sizing databases, in accordancewith embodiments of the present invention. The flow chart of FIG. 2includes steps 21-26.

In step 21, the database sizing program 17 receives a system input valueof each characteristic of multiple characteristics pertaining toutilization of multiple databases in the computer system 10. In oneembodiment, the multiple databases comprise the historic databases 18.

In one embodiment, the multiple characteristics comprise complexity ofdatabase queries including multiple joins.

In step 22, the database sizing program 17 identifies multiple patternsof historical capacity usage by the multiple databases. The multiplepatterns are statistically significant for the multiple characteristicsand encompass the input values.

Each pattern is statistically significant for either only a singlecharacteristic (individual pattern) or for two or more characteristics(relationship pattern). Each pattern encompasses the input value of eachcharacteristic for which each pattern is statistically significant.

The relative importance of each characteristic in a relationship patternis not accounted for explicitly, since the characteristics in arelationship pattern are effectively combined.

In one embodiment, the capacity usage is memory usage or centralprocessing unit (CPU) usage.

Each pattern may be statistically significant due to a first percent ofthe multiple databases having the input value, or being within apredetermined range of values that include the input value, for thesingle characteristic or for the two or more characteristics, subject tothe first percent being at least a predetermined percent.

In step 23, the database sizing program 17 computes, for each pattern, aweighted value of capacity usage (WCU) as a function of ACP, FC, and FD,wherein:

-   -   ACP=average number of capacity points for each pattern, which is        a value or measure of historical capacity usage for each        pattern;    -   FC=fraction of the characteristics associated with the pattern;        and    -   FD=fraction of databases used for the pattern.

In one embodiment, WCU=ACP*FC*FD, reflecting a linear dependence of WCUon FD for weighting the average number of capacity points (ACP).

In one embodiment, WCU depends nonlinearly on FD. For example WCU may beproportional to (FD)^(P) wherein P=0.5, 2, 3, any positive real numberexceeding 1, etc. As another example, WCU may be proportional toexp(A*(FD)) wherein A is a positive or negative real number.

In step 24, the database sizing program 17 computes a total value ofcapacity usage (TCU) as a sum of the weighted values (WCU) of capacityusage.

In step 25, the database sizing program 17 stores the computed totalvalue of capacity usage in a computer readable memory device of thecomputer system 10.

In step 26, the database sizing program 17 sizes at least one currentdatabase. The sizing utilizes the computed total value of capacityusage. The databases which are sized in step 26 are one or more currentdatabases. In one embodiment, the sizing of each database in thecomputer system requires the computer system to make available at least100% of the computed total value of capacity usage (TCU) to eachdatabase (e.g., 100%, 110%, 120%, 125%, 1300/%, 150%, etc.).

FIG. 3 is a flow chart describing deletion of at least onecharacteristic previously used to size current databases via the methodof FIG. 2, and resizing the current databases in consideration of thedeleted at least one characteristic, in accordance with embodiments ofthe present invention. The flow chart of FIG. 3 includes steps 31-33.

In step 31, the database sizing program 17 determines that an actualcapacity usage by the current databases over a period of time after thecurrent databases were sized is not within a predetermined percentage ofthe computed total value of capacity usage, and in response, identifyingat least one characteristic of the multiple characteristics for which anattempt to identify a statistically significant pattern wasunsuccessful.

For example, step 31 may attempt to determine whether 85% of the totalnumber of databases are within ±20% of the original capacity estimate,e.g., for 100 databases, the issue is whether at least 85 of the 100databases have actual memory usage within 20% N of the original capacityestimate of 1695 (i.e., between 1695-339 and 1695+339). The originalestimate of 1695 is the initially sized capacity estimate in EXAMPLE 1discussed infra.

The actual capacity usage may be a time-averaged capacity usage or anaverage peak capacity usage periodically (e.g., daily, weekly, etc.)within the period of time (e.g., a week, a month, three months, sixmonths, a year, etc.).

In step 32, the database sizing program 17 deletes the identified atleast one characteristic from the multiple characteristics.

In step 33, the database sizing program 17 resizes the current databasesbased on the at least one characteristic having been deleted from themultiple characteristics. The resizing of the current databases mayemploy the method of sizing databases described in FIG. 2.

In addition to deleting at least one characteristic in accordance withthe process of FIG. 3, the present invention may add characteristicsthat are statistically relevant and resize databases based on the addedcharacteristics using the method of sizing databases described in FIG.2.

Although the present invention is specific to sizing a database, thesame method/process can be used to size other middleware/technologies bycreating a series of repositories with usage data and determiningpatterns impacting capacity.

Example 1. Sizing Databases: Individual Patterns for Sizing CPU Capacity

A total number of characteristics (TC) is two characteristics, namelydatabase size and query complexity.

For the first characteristic of database size, a system input value of1.2 terabytes is received. An individual pattern Pattern_DS_1 isidentified and encompasses the input value of 1.2 terabytes. Thefraction of the characteristics (FC) associated with the patternPattern_DS_1 is 0.50 (i.e., one out of two characteristics). For thepattern Pattern_DS_1, the fraction of databases used for the pattern(FD) is 0.95 and the average number of capacity points historically usedby the pattern (ACP) is 1800; i.e., 95% of databases over 1 terabyte usebetween 1700 and 1900 capacity points. For this example, the weightedvalue of capacity usage for the first characteristic (WCU1) is equal toACP*FC*FD which is numerically equal to 855; i.e., (1800*0.50*0.95).

For the characteristic of query complexity, a system input value of 0.70is received. An individual pattern Pattern_QC_1 is identified andencompasses the input value of 0.70 (i.e., 70% of transactions runcomplex queries). The fraction of the characteristics (FC) associatedwith the pattern Pattern_QC_1 is 0.50 (i.e., one out of twocharacteristics). For the pattern Pattern_QC_1, the fraction ofdatabases used for the pattern (FD) is 0.80 and the average number ofcapacity points historically used by this pattern (ACP) is 2100; i.e.,80% of databases having 70% transactions running complex queries usebetween 2000 and 2200 capacity points. For this example, the weightedvalue of capacity usage for the second characteristic (WCU2) is equal toACP*FC*FD which is numerically equal to 840; i.e., (2100*0.50*0.80).

The total CPU capacity sizing estimate is a sum of the weighted valuesof capacity usage, namely WCU1+WCU2, which is numerically equal to 1695(i.e., 855+840).

Example 2. Sizing Databases: Relationship Pattern for Sizing CPUCapacity

A total number of characteristics (TC) is the same two characteristicsof EXAMPLE 1, namely database size and query complexity. In thisexample, the two characteristics are analyzed collectively in arelationship pattern PatternDS_QC_1 characterized by 98% of databasesfitting both Pattern_DS_1 and Pattern_QC_1 of EXAMPLE 1 use 1900-2100capacity points. Thus, for the pattern of Pattern_DS_QC_1, ACP=2000,FC=1.0 (i.e., the fraction of the characteristics associated with thepattern is 1.0, because 100% of the total number of characteristics isassociated with the relationship pattern Pattern_DS_QC_1), and FD=0.98,so that the weighted value of capacity usage (WCU) is equal to ACP*FC*FDwhich is numerically equal to 1960; i.e., (2000*1.0*0.98).

Example 3. Sizing Databases: Multiple Relationship Patterns for SizingCPU Capacity

A total number of seven characteristics (TC) are as follows with theirindicated system input values in parenthesis:

RDBMS (DB2_ESE);

PLATFORM (pSeries/AIX);

DATABASE_SIZE (500 GB);

BUFFERPOOL_SIZE (700 GB);

%_HIGH_COMPLEX_QUERIES (50);

%_MEDIUM_COMPLEX_QUERIES (40);

%_LOW_COMPLEX_QUERIES (10).

The seven characteristics are grouped into three relationship patternsas follows with the characteristics in parenthesis for each relationshippattern:

Relationship pattern: Pattern_DBM_1 (RDBMS, PLATFORM);

Relationship pattern: Pattern_DB_BP_5 (DATABASE_SIZE, BUFFERPOOL_SIZE);

Relationship pattern: Pattern_QY_MIX_12 (% HIGH_COMPLEX_QUERIES,%_MEDIUM_COMPLEX_QUERIES, %_LOW_COMPLEX_QUERIES).

Table 1 infra lists, for each of the three relationship patterns, thevalues of ACP (average number of capacity points for the pattern), FC(fraction of the characteristics associated with the pattern). FD(fraction of databases used for the pattern), and WCU (weighted value ofcapacity usage calculated via WCU=ACP*FC*FD).

TABLE 1 WCU For Relationship Patterns Relationship Pattern ACP FC FD WCUPattern_DBM_1 500 .286 .65 93.0 Pattern_DB_BP_5 5200 .286 .80 1189.8Pattern_QY_MIX_12 7800 .428 .85 2837.6 TOTAL 4120.4

The total value of capacity usage (4120.4) is the sum of the weightedvalues of capacity usage.

FIG. 4 is a flow chart of a method for sizing a capacity usage of acurrent database based on query complexity, in accordance withembodiments of the present invention. The flow chart of FIG. 4 includessteps 41-46.

In step 41, the database sizing program 17 receives specification of ananticipated relative rate of occurrence of queries to a current database19 at each query complexity level of a plurality of query complexitylevels, wherein the relative rate of occurrence of queries to thecurrent database corresponds to the % Use column of Tables 3 and 5described infra. The plurality of query complexity levels may consist oftwo query complexity levels or may comprise three or more querycomplexity levels.

In step 42, the database sizing program 17 receives specification of acapacity usage of each historic database of one or more historicdatabases 18, wherein the capacity usage is memory usage or centralprocessing unit (CPU) usage. In one embodiment, the one or more historicdatabases comprise two or more historic databases.

In step 43, the database sizing program 17 receives specification of oneor more queries such that each query has been executed in at least oneof the historic databases, each query independently being a specificquery or a type of query.

In step 44, the database sizing program 17 receives specification of oneof the query complexity levels for each query-database combination ofexecution of a query in a historic database. In one embodiment in whichthe one or more historic databases comprise two or more historicdatabases, at least one query of the one or more queries exists in atleast two of the query-database combinations pertaining to the historicdatabases.

In step 45, the database sizing program 17 receives specification of arelative rate of occurrence of queries for each query-databasecombination pertaining to the historic databases, wherein the relativerate of occurrence of queries for each query-database combinationpertaining to the historic databases corresponds to the % Use column ofTables 2 and 4 described infra.

In step 46, the database sizing program 17 sizes the capacity usage ofthe current database 19, based on (i) the capacity usage of eachhistoric database; (ii) the query complexity level for eachquery-database combination pertaining to the historic databases; (iii)the relative rate of occurrence of queries for each query-databasecombination pertaining to the historic databases; and (iv) the relativerate of occurrence of queries to the current database 19 at each querycomplexity level.

In one embodiment, the sizing the capacity usage of the new datadatabase comprises determining a weighted capacity usage for each querycomplexity level for the historic databases from which the capacityusage of the current database is sized. The determining the weightedcapacity usage for each query complexity level for the historicdatabases may be based on (i) the capacity usage of each historicdatabase; (ii) the query complexity level for each query-databasecombination pertaining to the historic databases; and (iii) the relativerate of occurrence of queries for each query-database combinationpertaining to the historic databases. The sizing the capacity usage ofthe current database may be based on (a) the weighted capacity usage foreach query complexity level for the historic databases and (b) therelative rate of occurrence of queries to the current database at eachquery complexity level.

In one embodiment, the determining the weighted capacity usage for eachquery complexity level for the historic databases comprises: for eachquery complexity level, computing the weighted capacity usage as afunction of the capacity usage of each historic database, and therelative rate of occurrence of queries, for each query-databasecombination at which said each complexity level was specified.

Example 4. Sizing Capacity Usage of a Current Database Based on QueryComplexity

Tables 2 and 3, depicted infra, illustrate use of the method of FIG. 4for sizing a capacity usage of a current database based on querycomplexity. Table 2 pertains to historical databases and Table 3pertains to current databases whose capacity usage (memory usage and CPUusage) are being sized based on query complexity.

TABLE 2 WCU For Historic Databases (Patterns Based on EET Range and DBSize) Query Weighted DB Comp: Memory Capacity Usage Size: DB % EET UsageCPU Pattern (WCU) DB (GB) Query Use EET Range (MB) Usage ID Memory CPUDB4 100 DB4Q1 70 .02 Low 256 82 P_02 309 MB 103 DB2 200 DB2Q1 80 .02 Low452 154 DB4 100 DB4Q2 30 .04 Low 256 82 DB1 500 DB1Q1 50 .09 Med 596 210P_06 272 MB 96 DB1 500 DB1Q2 30 .12 Med 596 210 DB2 200 DB2Q2 15 .25 Med452 154 DB3 600 DB3Q1 80 .12 Med 752 276 P_09 436 MB 159 DB3 600 DB3Q220 .14 Med 752 276 DB1 500 DB1Q3 20 .15 Med 596 210 DB2 200 DB2Q3 5 .32High 452 154 P_11  23 MB 8 DB5 800 DB5Q1 95 .33 High 2048 7800 P_12 2048MB  7800 DB5 800 DB5Q2 5 .41 High 2048 7800

TABLE 3 WCU For Current Database (Patterns Based on EET Range and DBSize) Historic Current Query DB DB Current DB Comp: WCU WCU DB Size: DBEET % Mem Mem (CDB) (GB) Query EET Range Use Pattern (MB) CPU (MB) CPUCDB1 230 CDB1Qa .08 Low 70 P_02 309 103 285 97 CDB1Qb .15 Med 25 P_06272 96 CDB1Qc .45 High 5 P_11 23 8 CDB2 600 CDB2Qa .14 Med 65 P_09 436159 1000 2833 CDB2Qb .45 High 35 P_12 2048 7800

The following considerations apply to all of the query complexitycategories or levels. The terms “query complexity category” and “querycomplexity level” have the same meaning. Terms appearing in Table 2 aredefined and explained as follows.

Average Elapsed Execution Time (“EET”) is defined as a start to finishtime taken to execute a single query and/or query type, which is anaverage (e.g., mean or any other average, such as a weighted average,known to one of ordinary skill in the art) of all executions of thatquery or query type per database. Calculating and baselining EET againsthistorical data allows the system to identify patterns. A long runningEET can identify a poorly tuned query or a highly complex query. Theelapsed execution time ranges per query complexity category may be iscomputed using the historical benchmark data ranges collected. Therecould be multiple EET ranges within a single query complexity categoryas the patterns become more refined based on historical usage. Moreover,a given query or query type may be associated with different querycomplexity categories for respective different databases to reflectdifferent execution time characteristics of different databases.

Database Size (“DB Size”) of each identified database, if increased, cannegatively impact the elapsed execution time for a given complex querycategory. The reverse is also true, since a relatively small databasewith a slow running query could indicate a poorly tuned query whichwould fall into the given complex query category. DB Size may bemeasured in gigabytes (GB) or any other standardized unit.

A database query (e.g., Q1, Q2, Q3, Q4, Q5 in Table 2) is a query and/orquery type executed by the application for each database.

A query-database combination (“DB Query”) identified, for example, asDB2Q1 means query Q1 executed in database DB2.

Percent Use (“% Use”) is percent or fractional rate of executions ofeach application query within a given database. For example, fordatabase DB2, the percent of executions in database DB2 of queries Q1.Q2, and Q3 are 80%, 15%, and 5%, respectively. The % Use column of Table2 illustrates the relative rate of occurrence, of queries for eachquery-database combination in the historic databases, used in step 45 ofFIG. 4.

Query Complexity (“Query Comp”) depends on EET Range. Generally, thereis a plurality of query complexity categories or levels. In an exemplarybut non-limiting embodiment illustrated in Tables 2 and 3, there arethree query complexity levels of Low, Medium, and High, with threeassociated EET ranges of less than 0.09, 0.09 to less than 0.32, and atleast 0.32, respectively, in an appropriate system of units such asmilliseconds, microseconds, etc.

The EET ranges may be determined, in one embodiment, as encompassingspecified percentages of all queries executed in all databases beingconsidered. For example, the EET ranges associated with the Low, Medium,and High query complexity levels may encompass 25%, 50%, and 25%,respectively, of all queries executed in all databases being considered(DB1-DB5).

Memory Usage and CPU Usage are specific to each database.

A pattern identified in the Pattern ID column of Table 2 is defined asencompassing all query-database combinations which fall within a givenEET range and a given DB Size range.

The P_02 pattern is defined as encompassing queries the Low querycomplexity level executed in a database having a DB Size less than orequal to 240 GB.

The P_06 pattern is defined as encompassing queries the Medium querycomplexity level executed in a database having a DB Size less than 550GB.

The P_09 pattern is defined as encompassing queries the Medium querycomplexity level executed in a database having a DB Size in a 500-700 GBrange.

The P_11 pattern is defined as encompassing queries the High querycomplexity level executed in a database having a DB Size less than 300GB.

The P_12 pattern is defined as encompassing queries the High querycomplexity level executed in a database having a DB Size greater than500 GB.

The Weighted Capacity Usage (WCU) for each capacity usage (i.e., MemoryUsage, CPU Usage) is computed, for each given pattern, as a linear ornonlinear function of the capacity usage of all of the query-databasecombinations associated with the given pattern, and represents anaverage WCU over all distinct databases utilized in the query-databasecombinations. Each query-database combination is weighted by % Use. InTable 2, WCU for Memory Usage and CPU Usage are each computed as aweighted summation over the Memory Usage and CPU Usage, respectively, ofall of the query-database combinations associated with the givenpattern, the weighting factor being % Use for each query-databasecombination. To obtain WCU, the summation is divided by the number ofdistinct databases in the query-database combinations utilized. Thecalculations of WCU in Table 2 are as follows.

For pattern P_02, WCU for Memory Usage:(256*0.70+452*0.80+256*0.30)/2=309 MB.

For pattern P_02, WCU for CPU Usage: (82*0.70+154*0.80+82*0.30)/2=103.

For pattern P_06, WCU for Memory Usage:(596*0.50+596*0.30+452*0.15)/2=272 MB.

For pattern P_06, WCU for CPU Usage: (210*0.50+210*0.30+154*0.15)/2=96.

For pattern P_09, WCU for Memory Usage:(752*0.80+752*0.20+596*0.20)/2=436 MB.

For pattern P_09, WCU for CPU Usage: (276*0.80+276*0.20+210*0.20)/2=159.

For pattern P_1, WCU for Memory Usage: (452*0.05)/1=23 MB.

For pattern P_11, WCU for CPU Usage: (154*0.05)/1=8.

For pattern P_12, WCU for Memory Usage: (2048*0.95+2048*0.05)/1=2048 MB.

For pattern P_12, WCU for CPU Usage: (7800*0.95+7800*0.05/1=7800

Table 3 illustrates the sizing of capacity usage (i.e., Memory Usage andCPU Usage) for two current databases identified as CDB1 and CDB2, usingthe WCU results for the patterns in Table 2. In Table 3, the columns ofDB Size, DB Query, EET, Query Comp, and % Use have the same meaning forthe current databases (CDB1, CDB2) that the corresponding columns inTable 3 have for the historical databases (DB1-DB5), except that the %Use column of Table 3 illustrates the relative rate of occurrence, ofqueries to the current database, used in step 41 of FIG. 4.

Table 3 shows database queries Qa, Qb, and Qc (or query types) beingexecuted in current database CDB1 with respective query-databasecombinations of CDB1Qa, CDB1Qb. and CDB1Qc. Table 3 also shows databasequeries (or query types) Qa, and Qb being executed in current databaseCDB2 with respective query-database combinations of CDB2Qa and CDB2Qb.

The patterns appearing in the Pattern column of Table 3 are patterns inTable 2 whose Low, Medium, and High query complexity levels and whosedefined DB Size in Table 2 encompass the corresponding query complexitylevels and DB Size of the given current databases of Table 3. TheHistorical DB WCU values for the patterns appearing in the Patterncolumn of Table 3 are used to compute the Current DB WCU values for thecurrent databases of Table 3. Accordingly, in Table 3, current databaseCDB1 uses the patterns P_02, P_06, and P_11 and respective DB WCU valuesin Table 2, and current database CDB2 uses the patterns P_09 and P_12and respective DB WCU values in Table 2.

The Current DB WCU values, as Memory Usage and CPU Usage for the currentdatabases CDB1 and CDB2, are computed as a linear or nonlinear functionof the Historical DB WCU values weighted by the % Use values in Table 3.In Table 3, the Current DB WCU values for Memory Usage and CPU Usage areeach computed as a weighted summation over the Historical DB WCU valuesof Memory Usage and CPU Usage, respectively, wherein the weightingfactor is % Use for each database query of the current database. Thecalculations of the Current DB WCU values in Table 3 are as follows.

For current database CDB1, Current DB WCU for Memory Usage is computedas: 309*0.70+272*0.25+23*0.05)=285 MB.

For current database CDB1, Current DB WCU for CPU Usage is computed as:103*0.70+96*0.25+8*0.05)=97.

For current database CDB2, Current DB WCU for Memory Usage is computedas: 436*0.65+2048*0.35=1000 MB.

For current database CDB2, Current DB WCU for CPU Usage is computed as:159*0.65+7800*0.35=2833.

Tables 4 and 5, depicted infra, illustrate use of the method of FIG. 4for sizing a capacity usage of a current database based on querycomplexity. Table 4 pertains to historical databases and Table 5pertains to current databases whose capacity usage (memory usage and CPUusage) are being sized based on query complexity.

TABLE 4 WCU For Historical Databases (Pattern Based on EET Range, DBSize, and QRS) Query Weighted DB Comp: Memory Capacity Usage Size: DB %EET Usage CPU Pattern (WCU) DB (GB) Query Use EET Range QRS % (MB) UsageID Memory CPU DB4 100 DB4Q1 70 .02 Low 5 256 82 P_02 309 MB 103 DB2 200DB2Q1 80 .02 Low 10 452 154 DB4 100 DB4Q2 30 .04 Low 8 256 82 DB1 500DB1Q1 50 .09 Med 14 596 210 P_06 272 MB 96 DB1 500 DB1Q2 30 .12 Med 8596 210 DB2 200 DB2Q2 15 .25 Med 2 452 154 DB3 600 DB3Q1 80 .12 Med 15752 276 P_09 436 MB 159 DB3 600 DB3Q2 20 .14 Med 21 752 276 DB1 500DB1Q3 20 .15 Med 25 596 210 DB2 200 DB2Q3 5 .32 High 2 452 154 P_11  23MB 8 DB5 800 DB5Q1 95 .33 High 42 2048 7800 P_12 2048 MB  7800 DB5 800DB5Q2 5 .41 High 45 2048 7800

TABLE 5 WCU For Current Database (Pattern Based on EET Range, DB Size,and QRS) Historic Current Query DB DB Current DB Comp: WCU WCU DB Size:DB EET % Mem Mem (CDB) (GB) Query EET Range QRS % Use Pattern (MB) CPU(MB) CPU CDB1 230 CDB1Qa .08 Low 8 70 P_02 309 103 285 97 CDB1Qb .15 Med9 25 P_06 272 96 CDB1Qc .45 High 3 5 P_11 23 8 CDB2 600 CDB2Qa .14 Med15 65 P_09 436 159 1000 2833 CDB2Qb .45 High 42 35 P_12 2048 7800

Tables 4 and 5 correspond to Tables 2 and 3, respectively, with theexception that Tables 4 and 5 have an additional column of Query ResultSet (QRS) which is an average percent of rows or database size returnedfrom the database for each query-database combination. QRS may indicatemore complexity and throughput if the result set is considered largerelative to historical benchmark data. QRS can further tailor the querypattern identified to refine the query types and patterns applied.Specifically, in Tables 4 and 5, QRS is used as a parameter to definethe patterns identified in FIGS. 4 and 5 and has no other impact on thesizing of the current databases.

A pattern identified in the Pattern ID column of Table 4 is defined asencompassing all query-database combinations which fall within a givenEET range, a given DB Size range, and a given QRS range.

The P_02 pattern is defined as encompassing queries the Low querycomplexity level executed in a database having a DB Size less than orequal to 240 GB and QRS less than 12%.

The P_06 pattern is defined as encompassing queries the Medium querycomplexity level executed in a database having a DB Size less than 550GB and QRS less than 15%.

The P_09 pattern is defined as encompassing queries the Medium querycomplexity level executed in a database having a DB Size in a 500-700 GBrange and QRS between 15 and 25%.

The P_11 pattern is defined as encompassing queries the High querycomplexity level executed in a database having a DB Size less than 300GB and QRS less than 5%.

The P_12 pattern is defined as encompassing queries the High querycomplexity level executed in a database having a DB Size greater than500 GB and QRS equal to 12%.

The patterns appearing in the Pattern column of Table 5 are patterns inTable 4 whose Low, Medium, and High query complexity levels and whosedefined DB Size and QRS in Table 4 encompass the corresponding querycomplexity levels and DB Size and QRS of the current databases (CDB1,CDB2) of Table 5.

Otherwise, all parameters and calculations described supra for Tables 2and 3 are performed in the same manner for Tables 4 and 5, respectively.

FIG. 5 illustrates a computer apparatus 90 (e.g., computer 14 of FIG. 1)used by the computer system 10 of FIG. 1 for sizing databases, inaccordance with embodiments of the present invention. The computersystem 10 includes a set of internal components 800 and externalcomponents 900. The set of internal components 800 includes one or moreprocessors 820, one or more computer-readable RAMs 822 and one or morecomputer-readable ROMs 824 on one or more buses 826, one or moreoperating systems 828 and one or more computer-readable storage devices830. The one or more operating systems 828 and program instructions forprograms 17 (see FIG. 1) are stored on one or more of the respectivecomputer-readable storage devices 830 for execution by one or more ofthe respective processors 820 via one or more of the respective RAMs 822(which typically include cache memory). In the illustrated embodiment,each of the computer-readable storage devices 830 is a magnetic diskstorage device of an internal hard drive. Alternatively, each of thecomputer-readable storage devices 830 is a semiconductor storage devicesuch as ROM 824, EPROM, flash memory or any other computer-readablestorage device that can store but does not transmit a computer programand digital information.

The set of internal components 800 also includes a R/W drive orinterface 832 to read from and write to one or more portablecomputer-readable storage devices 936 that can store but do not transmita computer program, such as a CD-ROM, DVD, memory stick, magnetic tape,magnetic disk, optical disk or semiconductor storage device. The programinstructions for programs 17-19 can be stored on one or more of therespective portable computer-readable storage devices 936, read via therespective R/W drive or interface 832 and loaded into the respectivehard drive or semiconductor storage device 830. The term“computer-readable storage device” does not mean signal propagationmedia such as copper transmission cables, optical transmission fibersand wireless transmission media.

The set of internal components 800 also includes a network adapter orinterface 836 such as a TCP/IP adapter card or wireless communicationadapter (such as a 4G wireless communication adapter using OFDMAtechnology). The programs instructions for programs 17-19 can bedownloaded to the respective computing/processing devices from anexternal computer or external storage device via a network (for example,the Internet, a local area network or other, wide area network orwireless network) and network adapter or interface 836. From the networkadapter or interface 836, the programs are loaded into the respectivehard drive or semiconductor storage device 830. The network may comprisecopper wires, optical fibers, wireless transmission, routers, firewalls,switches, gateway computers and/or edge servers.

The set of external components 900 includes a display screen (i.e.,touch screen) 920, a keyboard or keypad 930, and a computer mouse ortouchpad 940. The sets of internal components 800 also includes devicedrivers 840 to interface to display screen 920 for imaging, to keyboardor keypad 930, to computer mouse or touchpad 940, and/or to displayscreen for pressure sensing of alphanumeric character entry and userselections. The device drivers 840, R/W drive or interface 832 andnetwork adapter or interface 836 comprise hardware and software (storedin storage device 830 and/or ROM 824).

The programs can be written in various programming languages (such asJava, C+) including low-level, high-level, object-oriented or nonobject-oriented languages. Alternatively, the functions of the programscan be implemented in whole or in part by computer circuits and otherhardware (not shown).

While particular embodiments of the present invention have beendescribed herein for purposes of illustration, many modifications andchanges will become apparent to those skilled in the art. Accordingly,the appended claims are intended to encompass all such modifications andchanges as fall within the true spirit and scope of this invention.

What is claimed is:
 1. A method for sizing databases, the methodcomprising: identifying, by one or more processors, multiple patterns ofhistorical capacity usage by multiple databases in a computer system,wherein the multiple patterns are statistically significant for multiplecharacteristics due to the multiple databases including an input valueof each characteristic of multiple characteristics pertaining toutilization of the multiple databases, and wherein the capacity usage ismemory usage or central processing unit (CPU) usage; computing, for eachpattern, by the one or more processors, a weighted value of capacityusage (WCU) according to WCU=ACP*FC*PC, wherein ACP=an average value ofhistorical capacity points for each pattern, wherein FC=fraction of thecharacteristics associated with each pattern, and wherein PC=a fractionof the multiple databases used for each pattern; computing, by the oneor more processors, a total value of capacity usage as a sum of theweighted values of capacity usage; sizing, by the one or moreprocessors, one or more current databases via utilization of thecomputed total value of capacity usage; and creating, by the one or moreprocessors, a virtual machine or a new server of the computer system,wherein the virtual machine or new server comprises the sized one ormore current databases.
 2. The method of claim 1, wherein the one ormore current databases that are sized are a plurality of currentdatabases that are sized, the method further comprising: determining, bythe one or more processors, that an actual capacity usage by theplurality of current databases over a period of time after the pluralityof current databases were sized is not within a predetermined percentageof the computed total value of capacity usage, and in response,identifying at least one characteristic of the multiple characteristicsfor which an attempt to identify a statistically significant pattern wasunsuccessful; deleting, by the one or more processors, the identified atleast one characteristic from the multiple characteristics; andresizing, by the one or more processors, the plurality of currentdatabases based on the at least one characteristic having been deletedfrom the multiple characteristics.
 3. The method of claim 1, wherein themultiple characteristics comprise complexity of database queries whichinclude multiple joins.
 4. The method of claim 1, wherein each patternis statistically significant for either only a single characteristic ofthe multiple characteristics or for two or more characteristics of themultiple characteristics, and wherein each pattern encompasses the inputvalue of each characteristic for which each pattern is statisticallysignificant.
 5. The method of claim 4, wherein the multiple patternscomprise a pattern that is statistically significant for only a singlecharacteristic of the multiple characteristics.
 6. The method of claim4, wherein the multiple patterns comprise a pattern that isstatistically significant for two or more characteristics of themultiple characteristics.
 7. A computer program product, comprising oneor more computer readable hardware storage devices having computerreadable program code stored therein, said program code containinginstructions executable by one or more processors of a computer systemto implement a method for sizing databases, said method comprising:receiving, by the one or more processors, an input value of eachcharacteristic of multiple characteristics pertaining to utilization ofmultiple databases in a computer system; identifying, by the one or moreprocessors, multiple patterns of historical capacity usage by themultiple databases, wherein the multiple patterns are statisticallysignificant for the multiple characteristics due to the multipledatabases including the input values, and wherein the capacity usage ismemory usage or central processing unit (CPU) usage; computing, for eachpattern, by the one or more processors, a weighted value of capacityusage (WCU) according to WCU=ACP*FC*PC, wherein ACP=an average value ofhistorical capacity points for each pattern, wherein FC=fraction of thecharacteristics associated with each pattern, and wherein PC=a fractionof the multiple databases used for each pattern; computing, by the oneor more processors, a total value of capacity usage as a sum of theweighted values of capacity usage; sizing, by the one or moreprocessors, one or more current databases via utilization of thecomputed total value of capacity usage; and creating, by the one or moreprocessors, a virtual machine or a new server of the computer system,wherein the virtual machine or new server comprises the sized one ormore current databases.
 8. The computer program product of claim 7,wherein the one or more current databases that are sized are a pluralityof current databases that are sized, the method further comprising:determining, by the one or more processors, that an actual capacityusage by the plurality of current databases over a period of time afterthe plurality of current databases were sized is not within apredetermined percentage of the computed total value of capacity usage,and in response, identifying at least one characteristic of the multiplecharacteristics for which an attempt to identify a statisticallysignificant pattern was unsuccessful; deleting, by the one or moreprocessors, the identified at least one characteristic from the multiplecharacteristics; and resizing, by the one or more processors, theplurality of current databases based on the at least one characteristichaving been deleted from the multiple characteristics.
 9. The computerprogram product of claim 7, wherein the multiple characteristicscomprise complexity of database queries which include multiple joins.10. The computer program product of claim 7, wherein each pattern isstatistically significant for either only a single characteristic of themultiple characteristics or for two or more characteristics of themultiple characteristics, and wherein each pattern encompasses the inputvalue of each characteristic for which each pattern is statisticallysignificant.
 11. The computer program product of claim 10, wherein themultiple patterns comprise a pattern that is statistically significantfor only a single characteristic of the multiple characteristics. 12.The computer program product of claim 10, wherein the multiple patternscomprise a pattern that is statistically significant for two or morecharacteristics of the multiple characteristics.
 13. A computer system,comprising one or more processors, one or more memories, and one or morecomputer readable hardware storage devices, said one or more hardwarestorage device containing program code executable by the one or moreprocessors via the one or more memories to implement a method for sizingdatabases, said method comprising: receiving, by the one or moreprocessors, an input value of each characteristic of multiplecharacteristics pertaining to utilization of multiple databases in acomputer system; identifying, by the one or more processors, multiplepatterns of historical capacity usage by the multiple databases, whereinthe multiple patterns are statistically significant for the multiplecharacteristics due to the multiple databases including the inputvalues, and wherein the capacity usage is memory usage or centralprocessing unit (CPU) usage; computing, for each pattern, by the one ormore processors, a weighted value of capacity usage (WCU) according toWCU=ACP*FC*PC, wherein ACP=an average value of historical capacitypoints for each pattern, wherein FC=fraction of the characteristicsassociated with each pattern, and wherein PC=a fraction of the multipledatabases used for each pattern; computing, by the one or moreprocessors, a total value of capacity usage as a sum of the weightedvalues of capacity usage; sizing, by the one or more processors, one ormore current databases via utilization of the computed total value ofcapacity usage; and creating, by the one or more processors, a virtualmachine or a new server of the computer system, wherein the virtualmachine or new server comprises the sized one or more current databases.14. The computer system of claim 13, wherein the one or more currentdatabases that are sized are a plurality of current databases that aresized, the method further comprising: determining, by the one or moreprocessors, that an actual capacity usage by the plurality of currentdatabases over a period of time after the plurality of current databaseswere sized is not within a predetermined percentage of the computedtotal value of capacity usage, and in response, identifying at least onecharacteristic of the multiple characteristics for which an attempt toidentify a statistically significant pattern was unsuccessful; deleting,by the one or more processors, the identified at least onecharacteristic from the multiple characteristics; and resizing, by theone or more processors, the plurality of current databases based on theat least one characteristic having been deleted from the multiplecharacteristics.
 15. The computer system of claim 13, wherein themultiple characteristics comprise complexity of database queries whichinclude multiple joins.
 16. The computer system of claim 13, whereineach pattern is statistically significant for either only a singlecharacteristic of the multiple characteristics or for two or morecharacteristics of the multiple characteristics, and wherein eachpattern encompasses the input value of each characteristic for whicheach pattern is statistically significant.
 17. The computer system ofclaim 16, wherein the multiple patterns comprise a pattern that isstatistically significant for only a single characteristic of themultiple characteristics.
 18. The computer system of claim 16, whereinthe multiple patterns comprise a pattern that is statisticallysignificant for two or more characteristics of the multiplecharacteristics.